﻿using System;
using System.Collections.Generic;
using System.Text;
using SQLServerHealthCheck.Model;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;
using System.Linq;
using System.Collections;
using System.IO;

namespace SQLServerHealthCheck.SQLServer
{
    public static class CollectPerformanceData
    {

        public static List<Exception> errors;
        public static ArrayList arrPerformanceCheck = new ArrayList();
        public static List<Exception> Collect(List<String> SQLServerInstances)
        {
            try
            {
                errors = new List<Exception>();

                HealthCheckDataContext dc = new HealthCheckDataContext(System.Configuration.ConfigurationManager.AppSettings["ConnectionStringSSHCDatabase"].ToString());

                DeleteTablesAndIndexes();

                List<Server> servers = new List<Server>();

                arrPerformanceCheck.Clear();

                FillPerformanceData(SQLServerInstances, dc);
                FillColumnData(SQLServerInstances, dc);
                FillIndexColumnData(SQLServerInstances, dc);

            }
            catch (System.Security.SecurityException ex1)
            {
                MessageBox.Show("You don't have permission to delete file in path " + Application.StartupPath, "HealthCheck", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }

            return errors;



        }
        private static void DeleteTablesAndIndexes()
        {



            using (SqlConnection cn = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["ConnectionStringSSHCDatabase"]))
            {
                cn.Open();
                String sql = @" 
                            DELETE FROM [Index]                            
                            DELETE FROM [Table]
                            DELETE FROM [Column]
                            ";


                SqlCommand cmd = new SqlCommand(sql, cn);
                cmd.ExecuteNonQuery();





            }

        }

        private static void FillPerformanceData(List<String> SQLServerInstances, HealthCheckDataContext dc)
        {

            foreach (String sqlServerInstance in SQLServerInstances)
            {

                String[] parts = sqlServerInstance.ToLower().Split('\\');
                String serverName = parts[0];

                Server server = dc.Servers.FirstOrDefault(s => s.Name == serverName);

                if (server != null)
                {

                    Service service = server.Services.FirstOrDefault(s => s.InstanceName == sqlServerInstance);
                    if (service != null)
                    {


                        foreach (Database database in service.Databases)
                        {
                            if (
                                database.DatabaseName.Trim() != "master" &&
                                database.DatabaseName.Trim() != "model" &&
                                database.DatabaseName.Trim() != "distribution" &&
                                database.DatabaseName.Trim() != "msdb" &&
                                database.DatabaseName.Trim() != "tempdb" &&
                                database.DatabaseName.Trim() != "distribution" &&
                                !database.DatabaseName.Contains("ReportServer") &&
                                database.State.ToLower() != "offline"
                                )
                            {
                                FillTables(dc, database, service);

                                if (System.Convert.ToInt32(database.CompatibilityLevel) > 80)
                                {
                                    FillIndexes(dc, database, service);
                                }

                            }
                        }
                    }


                }

            }


        }

        private static void FillTables(HealthCheckDataContext dc, Database database, Service service)
        {

            try
            {
                //InstallationCheck.WriteLoginFile("SQLServerHealthCheck_PerformanceCheckLog.txt", "FillTables: ", " FillTables method in PerformanceCheck Started for " + service.InstanceName.ToString());

                //Check the connection to SQL Server Instance and gets the details.
                if (CollectServerAndServiceData.IsSQLServerAccessible(@"Server=" + service.InstanceName + ";database=" + database.DatabaseName + ";Integrated Security=True"))
                {


                    using (SqlConnection cn = new SqlConnection(@"Server=" + service.InstanceName + ";Database= "+database.DatabaseName+";Integrated Security=True"))
                    {
                        cn.Open();
                        String sql = @"CREATE TABLE #spaceused
                                                (
                                                    Name nvarchar(255),
                                                    Rows int,
                                                    [Total size] varchar(50),
                                                    [Data size] varchar(50),
                                                    [Index size] varchar(50),
                                                    Unused varchar(50)
                                                )
                                                DECLARE @table_name nvarchar(255) 

                                                DECLARE tables_cursor CURSOR LOCAL FORWARD_ONLY FOR
                                                    SELECT name FROM sysobjects WHERE type='U'
                                                OPEN tables_cursor
                                                FETCH NEXT FROM tables_cursor INTO @table_name
                                                WHILE @@FETCH_STATUS = 0
                                                BEGIN
                                                    INSERT INTO #spaceused EXEC sp_spaceused @table_name
                                                    FETCH NEXT FROM tables_cursor INTO @table_name
                                                END

                                                SELECT	Name,
														CAST([Rows] AS int),
														CAST(REPLACE( [Total size] , 'KB' , '' ) AS decimal) AS [Total size],
														CAST(REPLACE( [Data size], 'KB' , '' )AS decimal) AS[Data size],
														CAST(REPLACE([Index size],'KB' , '' )AS decimal) AS [Index size],
														CAST(REPLACE([Unused],'KB' , '' ) AS decimal) AS [Unused]
                                                 FROM #spaceused
                                                 
                                                 
                                                 ORDER BY CONVERT(int, LEFT([Total size],
                                                                     CHARINDEX(' KB', [Total size]))) DESC 

                                                DROP TABLE #spaceused
                                                CLOSE tables_cursor
                                                DEALLOCATE tables_cursor 	 
                                            
                                            ";



                        SqlCommand cmd = new SqlCommand(sql, cn);
                        SqlDataReader rd = cmd.ExecuteReader();

                        

                        while (rd.Read())
                        {
                            
                            
                            Table dt = new Table();
                            dt.TableName = rd.IsDBNull(0) ? null: rd[0].ToString();
                            dt.Rows = rd.IsDBNull(1) ? 0 : rd.GetInt32(1);
                            dt.TotalSize = rd.IsDBNull(2) ? 0 : rd.GetDecimal(2);
                            dt.DataSize = rd.IsDBNull(3) ? 0 : rd.GetDecimal(3);
                            dt.IndexSize = rd.IsDBNull(4) ? 0 : rd.GetDecimal(4);
                            dt.Unused = rd.IsDBNull(5) ? 0 : rd.GetDecimal(5);

                            database.Tables.Add(dt);
                        }


                        rd.Close();


                    }


                    dc.SubmitChanges();

                    //InstallationCheck.WriteLoginFile("SQLServerHealthCheck_PerformanceCheckLog.txt", "FillTables: ", " FillTables method in PerformanceCheck Completed for " + service.InstanceName.ToString());
                }

                else
                {
                    //Add the name of SQL Intances for which connections were not sucessfull and details were not imported

                    arrPerformanceCheck.Add(service.InstanceName.ToString());
                    //InstallationCheck.WriteLoginFile("SQLServerHealthCheck_PerformanceCheckLog.txt", "FillTables: ", " There were problems in importing PerformanceCheck details for " + service.InstanceName.ToString() + ".Please check the connectivity to this instance");
                }
            }


            catch (Exception ex)
            {



                errors.Add(ex);
                //InstallationCheck.WriteLoginFile("SQLServerHealthCheck_PerformanceCheckLog.txt", "FillTables: ", " Some error occured in Performance Check.Please check exception file for details");

            }



        }

        private static void FillIndexes(HealthCheckDataContext dc, Database database, Service service)
        {

            try
            {
                //InstallationCheck.WriteLoginFile("SQLServerHealthCheck_PerformanceCheckLog.txt", "FillTables: ", " FillTables method in PerformanceCheck Started for " + service.InstanceName.ToString());

                //Check the connection to SQL Server Instance and gets the details.
                if (CollectServerAndServiceData.IsSQLServerAccessible(@"Server=" + service.InstanceName.ToString() + ";database=master;Integrated Security=True"))
                {


                    using (SqlConnection cn = new SqlConnection(@"Server=" + service.InstanceName.ToString() + ";Database=master;Integrated Security=True"))
                    {
                        cn.Open();
                        String sql = @"	 	  Declare @Command Varchar(8000)
                                          Declare @a Varchar(8000),@Str Varchar(8000),@id int
                                          Declare @DBNVar1 Varchar(8000),
                                            @ObjIDVar1 Varchar(8000), 
                                            @ObjNameVar1 Varchar(8000),
                                            @IndIDVar1 Varchar(8000),
                                            @IndNAMEVar1 Varchar(8000),
                                            @IndexTypeVar Varchar(8000),
                                            @last_updated varchar(8000),                                           
                                            @RecommendationVar Varchar(8000) 



                                          Declare @Str1 Varchar(8000)
										  Declare @BIT bit
                                          Create Table ##IndNAME 
                                            (
                                            IndNAME Varchar(8000),
                                            IS_PK bit
                                            )

                                          Set @Str='' 
                                          Create Table ##SQL_FragINFO 
                                            (
                                            DatabaseName Varchar(8000),
                                            ObjectID Varchar(8000),
                                            ObjectName Varchar(8000),
                                            Index_ID Varchar(8000),
                                            Index_Type_DESC Varchar(8000),
                                            [IndexSize(MB)] decimal(10,2),
                                            last_updated datetime,
                                            Avg_Fragmentation_In_Percent decimal(5,2)
                                            )

                                          Create Table ##SQL_FragLIST 
                                            (
                                            DatabaseName Varchar(8000),
                                            ObjectID Varchar(8000),
                                            ObjectName Varchar(8000),
                                            Index_ID Varchar(8000),
                                            indexName varchar(8000),
                                            Index_Type_DESC Varchar(8000),
                                            [IndexSize(MB)] decimal(10,2),
                                            last_updated datetime,
                                            Avg_Fragmentation_In_Percent decimal(5,2),
                                            IS_PK bit,
                                            Recommendation varchar(8000)
                                            
                                            )

 
                                          declare @dbname varchar(8000)



                                Set @dbname='" + database.DatabaseName.ToString() + "'";

                                 sql = sql + @" 
                                          Select @Str='Use ['+Ltrim(@dbname)+'] 

                                          select 	DatabaseName,
                                                    ObjectId,
											ObjectName,IndexId,Index_Description,
                                            CONVERT(DECIMAL(16,1),(sum(avg_record_size_in_bytes * record_count)/ (1024.0 * 1024)))
                                            as ''IndexSize(MB)''
                                            ,last_updated,Avg_Fragmentation_In_Percent
                                            from( 
                                            SELECT Distinct DB_Name(Database_id) as ''DatabaseName'',
                                            Object_ID as ''ObjectId'',
                                            Object_Name(Object_id) as ''ObjectName'',
                                            Index_ID as ''IndexId'',
                                            Index_Type_Desc as ''Index_Description'',
                                            avg_record_size_in_bytes , record_count,
                                            STATS_DATE(object_id,index_id) as ''last_updated'',
                                            Convert(Varchar,round(Avg_Fragmentation_In_Percent,3)) as ''Avg_Fragmentation_In_Percent''
                                            FROM sys.dm_db_index_physical_stats (DB_ID('''+@dbname +'''), NULL, NULL, NULL, ''detailed'') 
                                            Where Object_id is not null and Avg_Fragmentation_In_Percent <> 0
                                            ) T
                                            group by 	DatabaseName,ObjectId,ObjectName,IndexId,Index_Description,last_updated,Avg_Fragmentation_In_Percent
                                            '
		
	
                                            Insert into ##SQL_FragINFO Exec (@Str)
	
                                            insert  Into ##SQL_FragLIST

                                            Select 
                                            DatabaseName,
                                            ObjectID,
                                            ObjectName,
                                            Index_ID,
                                            '******************NOT-AVAILABLE*********************' as IndexNAME,
                                            Index_Type_Desc,
                                            [IndexSize(MB)],
                                            last_updated,
                                            Avg_Fragmentation_In_Percent  as 'Avg_Fragmentation_In_Percent', 
											0 as IS_PK,
                                            'Recommendation'= 
                                            Case
                                            When (cast(isnull(Avg_Fragmentation_In_Percent,0) as numeric(6,3)) > 30.000 and Index_Type_Desc ='NONCLUSTERED INDEX') then 'REBUILD THIS INDEX'
                                            When (cast(isnull(Avg_Fragmentation_In_Percent,0) as numeric(6,3)) <= 30.000 and Index_Type_Desc ='NONCLUSTERED INDEX') then 'DEFRAG THIS INDEX'
                                            When (cast(isnull(Avg_Fragmentation_In_Percent,0) as numeric(6,3)) > 30.000 and Index_Type_Desc ='CLUSTERED INDEX') then 'REBUILD THIS INDEX'
                                            When (cast(isnull(Avg_Fragmentation_In_Percent,0) as numeric(6,3)) <= 30.000 and Index_Type_Desc ='CLUSTERED INDEX') then 'DEFRAG THIS INDEX'
                                            When (cast(isnull(Avg_Fragmentation_In_Percent,0) as numeric(6,3)) > 10.000 and Index_Type_Desc ='HEAP') then 'CREATE A CLUSTERED INDEX THEN DROP IT'
                                            When (cast(isnull(Avg_Fragmentation_In_Percent,0) as numeric(6,3)) <= 10.000 and Index_Type_Desc ='HEAP') then 'NO ACTION REQUIRED'
                                            End
                                            
                                            from ##SQL_FragINFO order by 1,2 desc

                                            Declare CheckFrag Cursor For 
                                            Select 
                                            DatabaseName,
                                            ObjectID,
                                            ObjectName,
                                            Index_ID,
                                            Index_Type_Desc, 
                                            last_updated,                                            
                                            Recommendation
                                            
                                            from ##SQL_FragLIST 
                                            Open CheckFrag
                                            Fetch Next from CheckFrag Into 
                                            @DBNVar1,
                                            @ObjIDVar1,
                                            @ObjNameVar1,
                                            @IndIDVar1,
                                            @IndexTypeVar,
                                            @last_updated,
                                            @RecommendationVar    

                                            While (@@Fetch_Status=0)
                                            Begin
												Truncate Table ##IndName
												Exec('Insert into ##IndNAME Select [Name],[is_primary_key] from ['+@DBNVar1+'].Sys.IndeXes  
												where Index_id = '+@IndIDVar1+' and object_id='+@ObjIDVar1)
			
												Select @Str1=IndNAME from ##IndNAME
												Select @BIT = Is_PK from ##IndNAME
												
												Update ##SQL_FragLIST
												Set IndexNAME = ISNULL(@Str1,'NOT-AVAILABLE'),
												Is_PK = ISNULL(@BIT,0)
												
												Where ObjectID=@ObjIDVar1 and 
												DatabaseName=@DBNVar1 

												Fetch Next from CheckFrag into 
												@DBNVar1,
												@ObjIDVar1,
												@ObjNameVar1,
												@IndIDVar1,
												@IndexTypeVar,
												@last_updated,
												@RecommendationVar    
                                            End
                                            Close CheckFrag
                                            Deallocate CheckFrag

                                            If (Select count(*) from ##SQL_FragLIST) >=1
                                            Begin
                                            Select DatabaseName,
                                            ObjectName,
                                            Index_ID,
                                            IndexNAME,
                                            Index_Type_DESC,
                                            [IndexSize(MB)],
                                            last_updated,
                                            Avg_Fragmentation_In_Percent, 
                                            Is_PK,
                                            Recommendation
                                            
                                            from ##SQL_FragLIST
                                            End

                                            Drop Table ##IndNAME 
                                            Drop Table ##SQL_FragINFO
                                            Drop Table ##SQL_FragLIST
                                            
                                            ";



                        SqlCommand cmd = new SqlCommand(sql, cn);
                        SqlDataReader rd = cmd.ExecuteReader();


                        while (rd.Read())
                        {
                            Table dt = null;
                            String tableName = rd[1].ToString();

                            dt = database.Tables.FirstOrDefault(d => d.TableName == tableName);
                            if (dt != null)
                            {
                                Index idf = new Index();
                                idf.IndexName = rd[3].ToString();
                                idf.IndexType = rd[4].ToString();
                                idf.IndexSize = rd.GetDecimal(5);
                                idf.Statistics_LastUpdatedDate = rd.IsDBNull(6) ? (DateTime?)null : rd.GetDateTime(6);
                                idf.Fragmentation = rd.GetDecimal(7);
                                idf.IS_PK = rd.GetBoolean(8);
                                idf.Recommendation = rd[9].ToString();

                                dt.Indexes.Add(idf);
                            }
                        }


                        rd.Close();


                    }


                    dc.SubmitChanges();

                    //InstallationCheck.WriteLoginFile("SQLServerHealthCheck_PerformanceCheckLog.txt", "FillTables: ", " FillTables method in PerformanceCheck Completed for " + service.InstanceName.ToString());
                }

                else
                {
                    //Add the name of SQL Intances for which connections were not sucessfull and details were not imported

                    arrPerformanceCheck.Add(service.InstanceName.ToString());
                    //InstallationCheck.WriteLoginFile("SQLServerHealthCheck_PerformanceCheckLog.txt", "FillTables: ", " There were problems in importing PerformanceCheck details for " + service.InstanceName.ToString() + ".Please check the connectivity to this instance");
                }
            }


            catch (Exception ex)
            {



                errors.Add(ex);
                //InstallationCheck.WriteLoginFile("SQLServerHealthCheck_PerformanceCheckLog.txt", "FillTables: ", " Some error occured in Performance Check.Please check exception file for details");

            }



        }
        
        private static void FillColumnData(List<string> SQLServerInstances, HealthCheckDataContext dc)
        {
            foreach (String sqlServerInstance in SQLServerInstances)
            {

                String[] parts = sqlServerInstance.ToLower().Split('\\');
                String serverName = parts[0];

                Server server = dc.Servers.FirstOrDefault(s => s.Name == serverName);

                if (server != null)
                {

                    Service service = server.Services.FirstOrDefault(s => s.InstanceName == sqlServerInstance);
                    if (service != null)
                    {



                        FillColumns(dc, service);




                    }


                }

            }
        }

        private static void FillColumns(HealthCheckDataContext dc, Service service)
        {
            try
            {
                //InstallationCheck.WriteLoginFile("SQLServerHealthCheck_PerformanceCheckLog.txt", "FillTables: ", " FillTables method in PerformanceCheck Started for " + service.InstanceName.ToString());

                //Check the connection to SQL Server Instance and gets the details.
                if (CollectServerAndServiceData.IsSQLServerAccessible(@"Server=" + service.InstanceName.ToString() + ";database=master;Integrated Security=True"))
                {


                    using (SqlConnection cn = new SqlConnection(@"Server=" + service.InstanceName.ToString() + ";Database=master;Integrated Security=True"))
                    {
                        cn.Open();
                        String sql = @"CREATE TABLE #TempColumn
                                                (
                                                DatabaseName nvarchar(255),
                                                TableName VARCHAR(255),                                               
                                                ColumnName VARCHAR(255),
                                                Column_Type VARCHAR(50),
                                                Size DECIMAL(18,0)
                                                )
                                                DECLARE @DatabaseName nvarchar(50)

                                                DECLARE GetDatabaseName CURSOR FOR
                                                SELECT d.name FROM sys.databases d 


                                                OPEN GetDatabaseName

                                                FETCH NEXT FROM GetDatabaseName INTO @DatabaseName

                                                WHILE @@FETCH_STATUS = 0

                                                BEGIN
	
                                                EXECUTE(
                                                '
                                                INSERT #TempColumn
                                                (
                                                DatabaseName ,
                                                TableName ,                                                
                                                ColumnName ,
                                                Size,
                                                Column_Type
                                                )
                                                SELECT 
                                                '''+@DatabaseName+''',
	                                                t.name AS TableName,	                                             
	                                                c.name AS ColumnName, 
	                                                c.max_length AS Size, 
	                                                st.name AS Column_Type  
                                                FROM 
	                                                ['+@DatabaseName+'].sys.tables AS t INNER JOIN	
	                                                ['+@DatabaseName+'].sys.columns AS c ON c.object_id = t.object_id INNER JOIN	                                                                                                
	                                                ['+@DatabaseName+'].sys.types st ON c.user_type_id = st.user_type_id
                                               
                                                ')

                                                FETCH NEXT FROM GetDatabaseName INTO @DatabaseName
                                                END
                                                CLOSE GetDatabaseName
                                                DEALLOCATE GetDatabaseName

                                                select*
                                                from #TempColumn
                                                Order By 
                                                DatabaseName ,
                                                TableName, 
                                                ColumnName
                                                
                                                DROP TABLE #TempColumn";



                        SqlCommand cmd = new SqlCommand(sql, cn);
                        SqlDataReader rd = cmd.ExecuteReader();


                        Index i = null;

                        Column c = null;
                        Table table = null;
                        Database database = null;

                        String previousDatabaseName = "";
                        String previousTableName = "";

                        while (rd.Read())
                        {

                            c = new Column();
                            string databaseName = rd[0].ToString();

                            if (previousDatabaseName == "" || databaseName != previousDatabaseName)
                            {

                                database = dc.Databases.FirstOrDefault(d => d.DatabaseName == databaseName);
                            }
                            if (database != null)
                            {
                                string tableName = rd[1].ToString();

                                if (previousTableName == "" || tableName != previousTableName)
                                {
                                    table = dc.Tables.FirstOrDefault(dt => dt.TableName == tableName);
                                }
                                
                                
                                if (table != null)
                                {
                                    string columnName = rd[2].ToString();                                    
                                    

                                    if (columnName != null)
                                    {

                                            c.Name = columnName;

                                            c.Size = rd.GetDecimal(4);
                                            c.DataType = rd[3].ToString();

                                       

                                           table.Columns.Add(c);

                                        
                                    }

                                }
                                previousDatabaseName = databaseName;
                                previousTableName = tableName;
                            }
                           
                        }

                        dc.SubmitChanges();
                        rd.Close();

                    }



                }




                    //InstallationCheck.WriteLoginFile("SQLServerHealthCheck_PerformanceCheckLog.txt", "FillTables: ", " FillTables method in PerformanceCheck Completed for " + service.InstanceName.ToString());


                else
                {
                    //Add the name of SQL Intances for which connections were not sucessfull and details were not imported

                    arrPerformanceCheck.Add(service.InstanceName.ToString());
                    //InstallationCheck.WriteLoginFile("SQLServerHealthCheck_PerformanceCheckLog.txt", "FillTables: ", " There were problems in importing PerformanceCheck details for " + service.InstanceName.ToString() + ".Please check the connectivity to this instance");
                }

            }


            catch (Exception ex)
            {



                errors.Add(ex);
                //InstallationCheck.WriteLoginFile("SQLServerHealthCheck_PerformanceCheckLog.txt", "FillTables: ", " Some error occured in Performance Check.Please check exception file for details");

            }
        }

        private static void FillIndexColumnData(List<String> SQLServerInstances, HealthCheckDataContext dc)
        {

            foreach (String sqlServerInstance in SQLServerInstances)
            {

                String[] parts = sqlServerInstance.ToLower().Split('\\');
                String serverName = parts[0];

                Server server = dc.Servers.FirstOrDefault(s => s.Name == serverName);

                if (server != null)
                {

                    Service service = server.Services.FirstOrDefault(s => s.InstanceName == sqlServerInstance);
                    if (service != null)
                    {



                        FillIndexColumns(dc, service);




                    }


                }

            }


        }

        private static void FillIndexColumns(HealthCheckDataContext dc, Service service)
        {

            try
            {
                //InstallationCheck.WriteLoginFile("SQLServerHealthCheck_PerformanceCheckLog.txt", "FillTables: ", " FillTables method in PerformanceCheck Started for " + service.InstanceName.ToString());

                //Check the connection to SQL Server Instance and gets the details.
                if (CollectServerAndServiceData.IsSQLServerAccessible(@"Server=" + service.InstanceName.ToString() + ";database=master;Integrated Security=True"))
                {


                    using (SqlConnection cn = new SqlConnection(@"Server=" + service.InstanceName.ToString() + ";Database=master;Integrated Security=True"))
                    {
                        cn.Open();
                        String sql = @"CREATE TABLE #TempColumn
                                                (
                                                DatabaseName nvarchar(255),
                                                TableName VARCHAR(255),
                                                IndexName VARCHAR(255),
                                                ColumnName VARCHAR(255),
                                                Column_Type VARCHAR(50),
                                                Size DECIMAL(18,0)
                                                )
                                                DECLARE @DatabaseName nvarchar(50)

                                                DECLARE GetDatabaseName CURSOR FOR
                                                SELECT d.name FROM sys.databases d 


                                                OPEN GetDatabaseName

                                                FETCH NEXT FROM GetDatabaseName INTO @DatabaseName

                                                WHILE @@FETCH_STATUS = 0

                                                BEGIN
	
                                                EXECUTE('
                                                INSERT #TempColumn
                                                (
                                                DatabaseName,
                                                TableName ,
                                                IndexName ,
                                                ColumnName ,
                                                Size,
                                                Column_Type
                                                )
                                                SELECT 
                                                '''+@DatabaseName+''',
	                                                t.name AS TableName,
	                                                i.name AS IndexName, 
	                                                c.name AS ColumnName, 
	                                                c.max_length AS Size, 
	                                                st.name AS Column_Type  
                                                FROM 
	                                                ['+@DatabaseName+'].sys.index_columns AS ic INNER JOIN
	                                                ['+@DatabaseName+'].sys.columns AS c ON ic.column_id = c.column_id AND ic.object_id = c.object_id INNER JOIN
	                                                ['+@DatabaseName+'].sys.tables AS t INNER JOIN
	                                                ['+@DatabaseName+'].sys.indexes AS i ON i.object_id = t.object_id ON ic.index_id = i.index_id AND ic.object_id = i.object_id INNER JOIN
	                                                ['+@DatabaseName+'].sys.types st ON c.user_type_id = st.user_type_id
                                                WHERE     (i.index_id > 0) AND (i.type IN (1, 2)) AND (i.is_primary_key = 0) AND (i.is_unique_constraint = 0) AND (i.is_disabled = 0) AND (i.is_hypothetical = 0) AND 
                                                                (ic.key_ordinal > 0)
                                                ORDER BY ic.key_ordinal')

                                                FETCH NEXT FROM GetDatabaseName INTO @DatabaseName
                                                END
                                                CLOSE GetDatabaseName
                                                DEALLOCATE GetDatabaseName

                                                select*
                                                from #TempColumn
                                                Order By 
                                                [DatabaseName],
                                                TableName ,
                                                IndexName 
                                                
                                                DROP TABLE #TempColumn";



                        SqlCommand cmd = new SqlCommand(sql, cn);
                        SqlDataReader rd = cmd.ExecuteReader();


                        Index idf = null;

                        Column c = null;
                        Table table = null;
                        IndexColumn ic = null;
                        while (rd.Read())
                        {
                            c = new Column();
                            ic = new IndexColumn();
                           
                            string databaseName = rd[0].ToString();

                            Database database = dc.Databases.FirstOrDefault(d => d.DatabaseName == databaseName);

                            if (database != null)
                            {
                                string tableName = rd[1].ToString();

                                table = database.Tables.FirstOrDefault(dt => dt.TableName == tableName);

                                if (table != null)
                                {
                                    string IName = rd[2].ToString();

                                    idf = table.Indexes.FirstOrDefault(i => i.IndexName == IName);

                                    if (idf != null)
                                    {

                                        string columnName = rd[3].ToString();

                                        c = dc.Columns.FirstOrDefault(col => col.Name == columnName);
                                        
                                        if(c !=null)
                                        {

                                            ic.Column = c;
                                            idf.IndexColumns.Add(ic);
                                        }




                                    }


                                }

                            }

                        }
                        dc.SubmitChanges();
                        rd.Close();

                    }



                }




                    //InstallationCheck.WriteLoginFile("SQLServerHealthCheck_PerformanceCheckLog.txt", "FillTables: ", " FillTables method in PerformanceCheck Completed for " + service.InstanceName.ToString());


                else
                {
                    //Add the name of SQL Intances for which connections were not sucessfull and details were not imported

                    arrPerformanceCheck.Add(service.InstanceName.ToString());
                    //InstallationCheck.WriteLoginFile("SQLServerHealthCheck_PerformanceCheckLog.txt", "FillTables: ", " There were problems in importing PerformanceCheck details for " + service.InstanceName.ToString() + ".Please check the connectivity to this instance");
                }

            }


            catch (Exception ex)
            {



                errors.Add(ex);
                //InstallationCheck.WriteLoginFile("SQLServerHealthCheck_PerformanceCheckLog.txt", "FillTables: ", " Some error occured in Performance Check.Please check exception file for details");

            }


        }

        
    }
}
